from copy import copy

import pandas as pd
import numpy as np
from datetime import datetime
import os
import shutil

import xlrd
from xlutils.copy import copy
import xlwt

from ExcelOp import ExcelOp


class PaiBan(object):
    def __init__(self, path=None, start=None, end=None):
        self.start_date = start
        self.end_date = end
        self.root = os.path.dirname(os.path.abspath(os.path.realpath(__file__)))
        self.path = path
        tmp = os.path.splitext(self.path)

        filename = "排班" + datetime.now().strftime("%Y%m%d") + "new" + tmp[1]
        dirname = os.path.join(os.path.dirname(self.root), "paiban")
        if not os.path.isdir(dirname):
            os.mkdir(dirname)
        self.new_path = os.path.join(dirname, filename)
        if os.path.isfile(self.new_path):
            os.remove(self.new_path)

    def run(self):
        pb_df = self.read_excel()
        # 添加日期列表
        self.add_columns(pb_df)
        # 拷贝生成新的排版表
        shutil.copyfile(self.path, self.new_path)

        # 读取课程表数据
        course_df = ExcelOp.search_df(self.start_date, self.end_date)
        # print(course_df)

        # 写数据
        self.write_excel(pd_df=pb_df, course_df=course_df)

    def read_excel(self):
        df = pd.read_excel(self.path, skiprows=3, usecols=[0, 1, 2, 3], dtype={"用户ID": str, "工号": str})
        return df.fillna('')

    def add_columns(self, df):
        dates = pd.date_range(self.start_date, self.end_date)
        # print(dates)
        for d in dates:
            d_t = d.strftime("%Y/%m/%d")
            df[d_t] = ""

    def write_excel(self, pd_df: pd.DataFrame, course_df: pd.DataFrame):
        course_df['date'] = course_df['date'].dt.date
        for c in course_df.index:
            if course_df.loc[c]['teacher'] is not np.nan:
                tc = course_df.loc[c]['teacher']
                dt = course_df.loc[c]['date']
                dt_str = dt.strftime("%Y/%m/%d")
                # print(dt.weekday())
                if dt.weekday() in [5, 6]:
                    pd_df.loc[pd_df['姓名'] == tc, [dt_str]] = "教学部周末上课考勤"
                else:
                    pd_df.loc[pd_df['姓名'] == tc, [dt_str]] = "教学部非周末上课考勤"

        self.write_excel_xls_append_columns(list(pd_df.columns), 3)
        pd_df = pd_df.applymap(lambda x:"休" if x == '' else x)
        self.write_excel_xls_append(pd_df=pd_df, startrows=4)

    def write_excel_xls_append_columns(self, value, start):
        index = len(value)  # 获取需要写入数据的行数
        workbook = xlrd.open_workbook(self.new_path)  # 打开工作簿
        new_workbook = copy(workbook)  # 将xlrd对象拷贝转化为xlwt对象
        new_worksheet = new_workbook.get_sheet(0)  # 获取转化后工作簿中的第一个表格
        for i in range(0, index):
            new_worksheet.write(start, i, value[i])  # 追加写入数据，注意是从i+rows_old行开始写入
        new_workbook.save(self.new_path)  # 保存工作簿
        print("xls格式表格【追加】写入字段成功！")

    def write_excel_xls_append(self, pd_df: pd.DataFrame, startrows=0):
        workbook = xlrd.open_workbook(self.new_path)  # 打开工作簿
        new_workbook = copy(workbook)  # 将xlrd对象拷贝转化为xlwt对象
        new_worksheet = new_workbook.get_sheet(0)  # 获取转化后工作簿中的第一个表格

        style = xlwt.XFStyle()
        max_cl = pd_df.shape[1]
        new_worksheet.write_merge(0, 0, 0, max_cl, "测试学科排班表", style)
        new_worksheet.write_merge(1, 1, 0, max_cl, "注意：时间必须严格按照现有格式，否则无法识别到钉钉考勤后台", style)
        new_worksheet.write_merge(2, 2, 0, max_cl,
                                  "排班信息：班次全公司正常作息时间: 08:45-18:00; 班次教学部周末上课考勤: 08:45-17:00; 班次教学部非周末上课考勤: 08:45-20:00; 清空：删除排休或排班; 休: 当天休息",
                                  style)
        for i in pd_df.index:
            tmp = pd_df.loc[i].to_list()
            for j in range(0, len(tmp)):
                if tmp[j]:
                    new_worksheet.write(i + startrows, j, tmp[j])
        new_workbook.save(self.new_path)  # 保存工作簿
        print("xls格式表格【追加】写入数据成功！")


if __name__ == '__main__':
    start_date = "2019/10/10"
    end_date = "2019/12/10"

    # start_date = input("开始时间：")
    # end_date = input("结束时间：")
    # path = input("排班表DEMO：")
    pb = PaiBan(path="测试学科排班表.xls", start=start_date, end=end_date)
    pb.run()
